Traditionally, databases have had difficulty accommodating both “as-is” and “as-was” reporting functionality in an efficient manner. An “as-is” query requests data using its current attribute values, regardless of whether some attribute values are different now than they were when the data was originally ingested into the database. For instance, in a database storing company metrics and business-related outcomes, an example of an “as-is” query would be a request for the past year's revenue numbers using the current commission hierarchy. In this query, “as-is” dimension attribute values (the current commission hierarchy) can be retrieved by a database that utilizes slowly changing dimension 1 (SCD-1 or type 1) data tables, in which new attribute values simply overwrite older attribute values. However, because type 1 architectures reflect only the most recent attribute values and overwrite historical values to do so, type 1 data tables are unable to perform historical data reporting.
An “as-was” query is a request for point-in-time reporting in which attributes are requested not using current attribute values, but using attributes as they existed at some prior point in time. Using the example database mentioned above, an example of an “as-was” request would be a request for the past year's revenue numbers using the commission hierarchy that existed at that time. In this query, “as-was” dimension attribute values (the historical commission hierarchy) cannot be retrieved using a type 1 architecture. To properly respond to the query, the database architecture must be able to retain historical attribute values, and a common method for retaining such information is to use a slowly changing dimension 2 (SCD-2 or type 2) technique in which the modification of an attribute value is made by adding a new record (containing the new attribute value) to a data table, rather than by overwriting attribute values of an existing record. By operating in this fashion, type 2 architectures retain all historical information, and thus provide the ability to respond to an “as-was” query.
Because type 1 architectures enable responding to “as-is” queries and type 2 architectures enable responding to “as-was” queries, database schemas for which both “as-is” and “as-was” reporting are required have historically been designed to include complete sets of type 1 dimensions and facts and to also include complete sets of type 2 dimensions and facts. However, this process requires redundant storage of data, which requires duplication of processing, development, testing, and maintenance of both the type 1 and the type 2 dimensions and facts.